SET SESSION AUTHORIZATION 'test_user';

SET search_path = myschema;

SET SESSION AUTHORIZATION DEFAULT;

DROP SCHEMA myschema CASCADE;

CREATE SCHEMA myschema AUTHORIZATION test_user;


SET SESSION AUTHORIZATION 'test_user';

REVOKE ALL ON SCHEMA test_schema FROM PUBLIC;
GRANT ALL ON SCHEMA test_schema TO PUBLIC;

CREATE SEQUENCE person_ids;

CREATE TABLE person (
    id INTEGER PRIMARY KEY DEFAULT NEXTVAL('person_ids'),
    name CHAR(64),
    surname CHAR(64)
);

CREATE SEQUENCE kind_ids;

CREATE TABLE pets_kind (
	id INTEGER PRIMARY KEY DEFAULT NEXTVAL('kind_ids'),
	kind CHAR(64)
);

CREATE SEQUENCE pets_ids;

CREATE TABLE pets (
	id INTEGER PRIMARY KEY DEFAULT NEXTVAL('pets_ids'),
	nick CHAR(64),
	owner INTEGER REFERENCES person,
	kind INTEGER REFERENCES pets_kind
);

INSERT INTO person (name, surname) VALUES ('Ivan', 'Ivanov');
INSERT INTO person (name, surname) VALUES ('Peter', 'Petrov');

INSERT INTO pets_kind (kind) VALUES ('dog');
INSERT INTO pets_kind (kind) VALUES ('cat');
INSERT INTO pets (nick, owner, kind) VALUES ('resc2', '1', '2');


DROP FUNCTION IF EXISTS 'generate_and_insert_data'; 
CREATE OR REPLACE FUNCTION generate_and_insert_data(max_num integer) RETURNS integer AS $$ 
DECLARE       
	name CHAR(64) := 'pet'; 
BEGIN        
       INSERT INTO pets select nextval('pets_ids'), name || count, '1', '2'
	 from generate_series(0,max_num) as count;                 
	RETURN 1; 
END;
$$ LANGUAGE plpgsql;
SELECT generate_and_insert_data(1000);

